from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from tkinter import font as tkfont
from threading import Thread
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
from sql import sqlseverDB
from config import *
import tkinter.messagebox as messagebox
import openpyxl,time,concurrent.futures



datatype=1
titlename='yntitle'
ordername='shopee_id_order'

class AdminFra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        DCMainFrame(self).pack(fill=X,padx=3,pady=3)

class DCMainFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        Label(self, text='印尼虾皮普货大数据-PHP', fg="black", bg="yellow",font=("微软雅黑", 13)).pack(fill=X,padx=5)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.kwcountStr=StringVar()
        self.kwcountStr.set('正在获取印尼普货已翻译产品数量...')
        self.first_load=True
        self.qqkws=None
        Thread(target=self.getkwcount).start()
        self.createWidgets()

    def createWidgets(self):
        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=2)
        self.cmb=mbDiv(self)
        
        f0=Frame(self)
        f0.pack(fill=X)

        lrf=Frame(f0)
        lrf.pack(side=LEFT,fill=Y,padx=2,pady=2)

        f00=Frame(lrf)
        f00.pack(fill=X,padx=2,pady=2)
        Label(f00, textvariable=self.kwcountStr,fg='red').pack(side=LEFT, padx=2, pady=2)
        
        label_font = tkfont.Font(family="微软雅黑", size=10, weight="bold")
        f01=Frame(lrf)
        f01.pack(fill=X,padx=2,pady=2)
        Label(f01, text='评分:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.PfIpt = Entry(f01,width=5)
        self.PfIpt.pack(side=LEFT, padx=2, pady=2)
        self.PfIpt.insert(0, '0')
        Label(f01, text='评价数量:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.CmtIpt = Entry(f01,width=5)
        self.CmtIpt.pack(side=LEFT, padx=2, pady=2)
        self.CmtIpt.insert(0, '0')
        Label(f01, text='月销量:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.SkuSoldIpt = Entry(f01,width=5)
        self.SkuSoldIpt.pack(side=LEFT, padx=2, pady=2)
        self.SkuSoldIpt.insert(0, '1')
        Label(f01, text='总销量:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.TotalSoldIpt = Entry(f01,width=5)
        self.TotalSoldIpt.pack(side=LEFT, padx=2, pady=2)
        self.TotalSoldIpt.insert(0, '1')

        f02=Frame(lrf)
        f02.pack(fill=X,padx=2,pady=2)
        Label(f02, text='汇率参数:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.HlIpt = Entry(f02,width=5)
        self.HlIpt.pack(side=LEFT, padx=2, pady=2)
        self.HlIpt.insert(0, '420')
        Label(f02, text='价格区间:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.MinPriceIpt = Entry(f02,width=6)
        self.MinPriceIpt.pack(side=LEFT, padx=2, pady=2)
        self.MinPriceIpt.insert(0, '800')
        Label(f02,text='~').pack(side=LEFT, padx=2, pady=2)
        self.MaxPriceIpt = Entry(f02,width=6)
        self.MaxPriceIpt.pack(side=LEFT, padx=2, pady=2)
        self.MaxPriceIpt.insert(0, '6000')
        
        Label(f02, text='价格倍数:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.PbsIpt = Entry(f02,width=6)
        self.PbsIpt.pack(side=LEFT, padx=2, pady=2)
        self.PbsIpt.insert(0, '1')

        f03=Frame(lrf)
        f03.pack(fill=X,padx=2,pady=2)
        Label(f03, text='产品数:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.EveIpt = Entry(f03,width=6)
        self.EveIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f03, text='表份数:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.FileCountIpt = Entry(f03,width=6)
        self.FileCountIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f03, text='模版数:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.MbCountIpt = Entry(f03,width=6)
        self.MbCountIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f03, text='授权码:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.SqmIpt = Entry(f03,width=6)
        self.SqmIpt.pack(side=LEFT, padx=2, pady=2)
        self.SqmIpt.insert(0, 'cai5415')

        f04=Frame(lrf)
        f04.pack(fill=X,padx=2,pady=2)
        self.dcbtn=Button(f04,text='导出(印尼标题)',command=lambda:Thread(target=self.start_dc).start(),fg='#ffffff',bg='green',width=20)
        self.dcbtn.pack(side=LEFT, padx=2, pady=2)

        self.dcbtn2=Button(f04,text='导出(通用模版)',command=lambda:Thread(target=self.start_dcty).start(),fg='#ffffff',bg='green',width=20)
        self.dcbtn2.pack(side=LEFT, padx=2, pady=2)

        rf=Frame(f0)
        rf.pack(side=RIGHT,fill=Y)
        rollDiv(rf)

    def getkwcount(self):
        
        while True:
            try:
                if self.first_load:
                    wstr_price=''
                    self.first_load=False
                else:
                    minp=float(self.MinPriceIpt.get().strip())
                    maxp=float(self.MaxPriceIpt.get().strip())
                    wstr_price= f'and price between {minp} and {maxp}'
                rskw=self.ms.ExecQuery('select qcontent from qqc where qtype=?',('印尼语普货',))
                self.qqkws=[r[0] for r in rskw]
                rs1=self.ms.ExecQuery(f'''SELECT COUNT(distinct itemid) from ProShopeePh where datatype={datatype} 
                                      and {titlename} is not null and {ordername}=0 and daochu>=0''')
                rs2=self.ms.ExecQuery(f'''SELECT COUNT(distinct itemid) from ProShopeePh where daochu=0 and datatype={datatype} 
                                      and {titlename} is not null and {ordername}=0 {wstr_price}''')
                if rs2[0][0]<5000:
                    self.ms.ExecNoQuery(f'''update ProShopeePh set daochu=0 where daochu>0 and datatype={datatype} 
                                            and {ordername}=0''')
                
                self.kwcountStr.set(f'印尼普货共有产品数据 {rs1[0][0]} 个,其中 {rs2[0][0]} 个产品未导出')
                

            except Exception as e:
                print(f'获取铺货产品数量网络错误=>{e}')
            time.sleep(60)
    
    def get_rp(self,itemids):
        wstr=','.join(['?' for _ in itemids])
        sqlstr=f'select * from proshopeeph where itemid in ({wstr})'
        rp=ms.ExecQuerydict(sqlstr,itemids)
        new_itemids=[]
        new_rp=[]
        yjc_itemid={}
        for r in rp:

            itemid=r['itemid']
            yntile=r['yntitle']
            if yjc_itemid.get(itemid):
                if yjc_itemid[itemid]=='不侵权':
                    new_rp.append(r)
            else:
                jiaojikws=contains_word(yntile,self.qqkws)
                if jiaojikws:
                    kpara=list(jiaojikws)
                    wstr=','.join(['?' for _ in kpara])
                    aff1=self.ms.ExecNoQuery(f'update qqc set usecount=usecount+1 where qtype=? and qcontent in ({wstr})',['印尼语普货']+kpara)
                    aff2=self.ms.ExecNoQuery('update proshopeeph set daochu=-5 where itemid=?',(itemid,))
                    print(f'《proshopeeph》,itemid:{itemid},触发侵权词{jiaojikws}:{aff1},加入黑名单:{aff2}')
                    yjc_itemid[itemid]='侵权'
                else:
                    new_rp.append(r)
                    new_itemids.append(itemid)
                    yjc_itemid[itemid]='不侵权'


        return new_itemids,new_rp

    def start_dc(self):

        smtys={
            '标题':f'{titlename}Rcode',
            '产品ID':'itemid',
            '详细参数':'attributes',
            'SKU图片网址':'skuImg',
            '价格':'skuPirce',
            'SKUID':'skuid',
            '描述':'pdec'
        }

        zjys={
            'Option for Variation 1':'sku1',
            'Option for Variation 2':'sku2',
        }

        if not self.EveIpt.get().strip() or not self.FileCountIpt.get().strip() or not self.MbCountIpt.get().strip():
            messagebox.showinfo('提示','请输入完整导出信息')
            return

        sqm=self.SqmIpt.get().strip()
        if sqm!='cai5415':
            messagebox.showinfo('提示','请导入正确的授权码')
            return
        self.dcbtn.config(state=DISABLED)
        while True:
            if self.qqkws is not None:
                print(f'共获取普货侵权词:{len(self.qqkws)} 个')
                break
        minp=float(self.MinPriceIpt.get().strip())
        maxp=float(self.MaxPriceIpt.get().strip())
        pf=float(self.PfIpt.get().strip())
        yxl=int(self.SkuSoldIpt.get().strip())
        zxl=int(self.TotalSoldIpt.get().strip())
        cmt_count=int(self.CmtIpt.get().strip())
        everycount=int(self.EveIpt.get().strip())
        filecount=int(self.FileCountIpt.get().strip())
        phl=float(self.HlIpt.get().strip())
        pbs=float(self.PbsIpt.get().strip())
        mbcount=int(self.MbCountIpt.get().strip())

        px=self.cmb.px
        print(f'筛选本次{px}可导出的模版ID...')
        where_str=f'{ordername}=0 and price between {minp} and {maxp}'
        if px!='yndccount':
            where_str='search_clicks>0' if px=='click_count' else 'my_sold between 1 and 2'
        sqlstr2=f'''
                select mb1.did,
                    (SELECT COUNT(DISTINCT itemid) from ProShopeePh where pmbid=mb1.did  and {titlename} is not null 
                    and daochu=0 and {where_str}) as yndccount
                    from mb as mb1 
                    where duid=4 
                    and dstate=2 
                    and dxs=1
                    and mb1.dtype={datatype}
                    order by yndccount desc
            '''
        rs=ms.ExecQuery(sqlstr2)
        print(f'按 {px} 排序,共获取 {len(rs)} 个MBid,需导出 {mbcount} 个模版')

        need_pcount=everycount*filecount
        has_dc_mb_count=0
        dcmr=f'导出结果_{px}_{getTimeStr()}'
        os.mkdir(dcmr)
        for mbid,yndccount in rs:
            if yndccount==0:
                print(f'模板 {mbid} 当前可导出数据为0跳过')
                break
            self.cpcount=0
            print(f'正在获取模板 {mbid} 的数据...')
            if px=='yndccount':
                sql_dc=f'''select top {need_pcount} itemid from proshopeeph 
                                where pmbid=? and {ordername}=0  and {titlename} is not null and daochu=0 group by itemid'''
            elif px=='click_count':
                sql_dc=f'''select top {need_pcount} itemid from proshopeeph 
                    where pmbid=? and {titlename} is not null and daochu=0 and search_clicks>0 group by itemid'''
            elif px=='sold_count':
                sql_dc=f'''select top {need_pcount} itemid from proshopeeph 
                    where pmbid=? and {titlename} is not null and daochu=0 and my_sold between 1 and 2 group by itemid'''
                
            rs=ms.ExecQuery(sql_dc,(mbid,))
            print(f'模版:{mbid},需要导出 {need_pcount} 个')
            pros=[]
            dc_itemids=[]
            qknum=5
            with concurrent.futures.ThreadPoolExecutor(max_workers=20) as t0:

                tasks=[]
                for j in range(0, len(rs), qknum):
                    cpids=[r[0] for r in rs[j:j+qknum]]

                    tasks.append(t0.submit(self.get_rp,cpids))
                
                for t in concurrent.futures.as_completed(tasks):

                    cur_itemids,rp=t.result()
                    smt_rp=getsmtpro(rp)
                    pros.extend(smt_rp)
                    dc_itemids.extend(cur_itemids)
                    self.cpcount+=qknum
                    print(f'模版:{mbid},成功导出{len(smt_rp)}个产品,当前导出量:{self.cpcount},剩余导出:{need_pcount-self.cpcount}')

                
            print(f'模板id:{mbid},成功获取产品：{len(pros)},需标记pid:{len(dc_itemids)}')

            dc_exceldata(pros,mbid,everycount,filecount,phl,pbs,dcmr,smtys,zjys)
            wstr=','.join(['?' for _ in dc_itemids])
            aff2=ms.ExecNoQuery(f'update proshopeeph set daochu=1 where itemid in ({wstr})',dc_itemids)
            
            print(f'模版id:{mbid},成功标注:{aff2}个产品导出状态')
            print(f'模板id:{mbid},成功导出(印尼标题)')
            print()
            has_dc_mb_count+=1
            if has_dc_mb_count>=mbcount:
                break
        print(f'全部导出完毕(印尼标题)')
        messagebox.showinfo('提示',f'全部导出完毕(印尼标题)')
        self.dcbtn.config(state=NORMAL)
    
    def start_dcty(self):
        smtys={
            '标题':'yntitleRcode',
            '产品ID':'itemid',
            '详细参数':'attributes',
            'SKU图片网址':'skuImg',
            '价格':'skuPirce',
            'SKUID':'skuid',
            '描述':'pdec'
        }

        zjys={
            'Option for Variation 1':'sku1',
            'Option for Variation 2':'sku2',
        }

        if not self.EveIpt.get().strip() or not self.FileCountIpt.get().strip():
            messagebox.showinfo('提示','请输入完整导出信息')
            return

        sqm=self.SqmIpt.get().strip()
        if sqm!='cai5415':
            messagebox.showinfo('提示','请导入正确的授权码')
            return
        self.dcbtn2.config(state=DISABLED)  


        minp=float(self.MinPriceIpt.get().strip())
        maxp=float(self.MaxPriceIpt.get().strip())
        pf=float(self.PfIpt.get().strip())
        yxl=int(self.SkuSoldIpt.get().strip())
        zxl=int(self.TotalSoldIpt.get().strip())
        cmt_count=int(self.CmtIpt.get().strip())
        everycount=int(self.EveIpt.get().strip())
        filecount=int(self.FileCountIpt.get().strip())
        phl=float(self.HlIpt.get().strip())
        pbs=float(self.PbsIpt.get().strip())
        mbid=11105
        print(f'正在通用模版导出,通用模版ID为:{mbid}')

        need_pcount=everycount*filecount    
        self.cpcount=0
        print(f'正在获取模板 {mbid} 的数据...')

        px=self.cmb.px

        if px=='yndccount':
            sql_dc=f'''select top {need_pcount} itemid from proshopeeph 
                            where {ordername}=0  and {titlename} is not null and daochu=0 group by itemid'''
        elif px=='click_count':
            sql_dc=f'''select top {need_pcount} itemid from proshopeeph 
                where {titlename} is not null and daochu=0 and search_clicks>0 group by itemid'''
        elif px=='sold_count':
            sql_dc=f'''select top {need_pcount} itemid from proshopeeph 
                where {titlename} is not null and daochu=0 and my_sold between 1 and 2 group by itemid'''
            
        rs=ms.ExecQuery(sql_dc)
        print(f'模版:{mbid},需要导出 {need_pcount} 个')
        dcmr=f'导出结果(通用模版)_{px}_{getTimeStr()}'
        os.mkdir(dcmr)
        pros=[]
        dc_itemids=[]
        qknum=5
        with concurrent.futures.ThreadPoolExecutor(max_workers=20) as t0:

            tasks=[]
            for j in range(0, len(rs), qknum):
                cpids=[r[0] for r in rs[j:j+qknum]]

                tasks.append(t0.submit(self.get_rp,cpids))
            
            for t in concurrent.futures.as_completed(tasks):

                cur_itemids,rp=t.result()
                smt_rp=getsmtpro(rp)
                pros.extend(smt_rp)
                dc_itemids.extend(cur_itemids)
                self.cpcount+=qknum
                print(f'模版:{mbid},成功导出{len(smt_rp)}个产品,当前导出量:{self.cpcount},剩余导出:{need_pcount-self.cpcount}')

                
            print(f'模板id:{mbid},成功获取产品：{len(pros)},需标记pid:{len(dc_itemids)}')

            dc_exceldata(pros,mbid,everycount,filecount,phl,pbs,dcmr,smtys,zjys)
            wstr=','.join(['?' for _ in dc_itemids])
            aff2=ms.ExecNoQuery(f'update proshopeeph set daochu=1 where itemid in ({wstr})',dc_itemids)
            print(f'模版id:{mbid},成功标注:{aff2}个产品导出状态')
            print(f'模板id:{mbid},成功导出(通用模版)')
            print()
        print(f'全部导出完毕(通用模版)')
        messagebox.showinfo('提示',f'全部导出完毕(通用模版)')
        self.dcbtn2.config(state=NORMAL)

class mbDiv(Frame):
    def __init__(self,master=None):
        Frame.__init__(self, master)
        self.ms=sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.pack(fill=X,pady=2,padx=2)
        self.createWidgets()
        self.fatherfra=master
        self.px='yndccount'
        Thread(target=self.getmbData).start()
        Thread(target=self.getmbcount).start()
        
    def createWidgets(self):
        
        self.mbStr=StringVar()
        self.mbStr.set('正在获取模板数据...')
        f2=Frame(self)
        f2.pack(fill=X,padx=2,pady=2)
        Label(f2, textvariable=self.mbStr,fg='red').pack(side=LEFT, padx=2, pady=2)
        


        self.firstjz=True
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        columns = ("did","dpcount","ynwdpcount","click_count","currency")
        tree = ttk.Treeview(f0, show = "headings", columns = columns)
        tree.column("did", anchor = "center",width=150)
        tree.column("dpcount", anchor = "center",width=150)
        tree.column("ynwdpcount", anchor = "center",width=150)
        tree.column("click_count", anchor = "center",width=150)
        tree.column("currency", anchor = "center",width=150)


        tree.heading("did", text = "模板id")
        tree.heading("dpcount", text = "产品总数量")
        tree.heading("ynwdpcount", text = "可导出数量")
        tree.heading("click_count", text = "有流量数量")
        tree.heading("currency", text = "出单量")
        
        self.VScroll1 = Scrollbar(f0, orient='vertical', command=tree.yview)
        self.VScroll1.pack(side=RIGHT,fill=Y)
        tree.configure(yscrollcommand=self.VScroll1.set)
        tree.pack(padx=3)
        self.tree=tree


        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)

        self.btn_catmb=Button(f1,text='刷新',command=lambda:Thread(target=self.upmb).start(),fg='#ffffff',bg='green',width=20)
        self.btn_catmb.pack(side=LEFT, padx=2, pady=2)

        self.btn_upydata=Button(f1,text='从流量表更新流量和销量',command=lambda:Thread(target=self.upydata).start(),fg='#ffffff',bg='green',width=20)
        self.btn_upydata.pack(side=LEFT, padx=2, pady=2)
        
        self.btn_show_ll=Button(f1,text='模版按流量排序',command=lambda:Thread(target=self.upmb,args=('click_count',)).start(),fg='#ffffff',bg='green',width=20)
        self.btn_show_ll.pack(side=LEFT, padx=2, pady=2)

        self.btn_show_xl=Button(f1,text='模版按销量排序',command=lambda:Thread(target=self.upmb,args=('sold_count',)).start(),fg='#ffffff',bg='green',width=20)
        self.btn_show_xl.pack(side=LEFT, padx=2, pady=2)

    def getmbcount(self):
 
        while True:
            try:
                self.ms.ExecNoQuery('''update mb set dpstatus=1 where did in 
                                    (select distinct pmbid from ProShopeePh where pmbid!=0 and daochu>=0) 
                                    and duid=4 and dpstatus=0''')
                rs0=self.ms.ExecQuery('select count(did) from mb where duid=4 and dtype=1 and dxs=1')
                rs1=self.ms.ExecQuery('select count(did) from mb where duid=4 and dstate=2 and dtype=1 and dxs=1')
                rs2=self.ms.ExecQuery('select count(did) from mb where duid=4 and dstate=2 and dpstatus=1 and dtype=1 and dxs=1')
                self.mbStr.set(f'共有 {rs0[0][0]} 套模板,{rs1[0][0]} 套模板已经完成,其中有数据模板有 {rs2[0][0]} 套')
                
            except Exception as e:
                print(e)
            time.sleep(120)

    def upydata(self):
        self.btn_upydata.config(state=DISABLED)
        aff=self.ms.call_procedure('UpdateSearchClicks')
        print(f'更新流量和销量行数:{aff}')
        messagebox.showinfo('提示',f'更新流量和销量行数:{aff}')
        self.upmb(px='click_count')
        self.btn_upydata.config(state=NORMAL)
        

    def upmb(self,px='yndccount'):
        self.btn_catmb.config(state=DISABLED)
        minp=float(self.fatherfra.MinPriceIpt.get().strip())
        maxp=float(self.fatherfra.MaxPriceIpt.get().strip())
        self.px=px
        print(f'按价格区间 {minp} ~ {maxp},{px} 排序')
        self.getmbData([minp,maxp],px)
        if px!='yndccount':
            Thread(target=self.ck_count,args=(px,)).start()
        self.btn_catmb.config(state=NORMAL)
    

    def ck_count(self,px):
        where_str='search_clicks>0' if px=='click_count' else 'my_sold between 1 and 2'
        rs1=self.ms.ExecQuery(f'select count(distinct itemid) from proshopeeph where daochu=0 and {where_str}')
        rs2=self.ms.ExecQuery(f'select count(distinct itemid) from proshopeeph where {where_str}')
        px_not_dc_num=rs1[0][0]
        px_num=rs2[0][0]
        if px_num>0:
            if px_not_dc_num/px_num<=0.1:

                aff=self.ms.ExecNoQuery(f'update proshopeeph set daochu=0 where daochu=1 and {where_str}')
                print(f'{px}导出方式数量不足,重置所有{px}的导出状态为0,共重置{aff}行')
        

    def getmbData(self,price_range=None,px='yndccount'):
        if not self.firstjz:
            for _ in map(self.tree.delete, self.tree.get_children("")):
                pass
        else:
            self.firstjz=False
        if price_range:
            wstr_price= f'and price between {price_range[0]} and {price_range[1]}'
        else:
            wstr_price=''
        
        where_str=f'and {ordername}=0 {wstr_price}'
        if px=='click_count':
            where_str='and search_clicks>0'
        if px=='sold_count':
            where_str='and my_sold between 1 and 2'

        sqlstr=f'''select mb1.did,
                        (SELECT COUNT(DISTINCT itemid) from ProShopeePh where pmbid=mb1.did and {titlename} is not null 
                        and daochu>=0 ) as yncount,
                        (SELECT COUNT(DISTINCT itemid) from ProShopeePh where pmbid=mb1.did and {titlename} is not null 
                        and daochu=0 {where_str}) as yndccount,
                        (SELECT COUNT(DISTINCT itemid) from ProShopeePh where pmbid=mb1.did
                        and search_clicks>0) as click_count,
                        (SELECT COUNT(DISTINCT itemid) from ProShopeePh where pmbid=mb1.did
                        and my_sold>0) as sold_count
                        from mb as mb1 where duid=4 and dstate=2 and dtype!=2 order by yndccount desc
                    '''
        
        rs=ms.ExecQuery(sqlstr)
        i=1
        for v in rs:
            if v[1]==0:
                continue
            cv=list(v)
            cvalues=cv
            self.tree.insert('', i, values =cvalues)
            i += 1

class rollDiv(Frame):

    def __init__(self,master=None):
        Frame.__init__(self, master)
        self.pack(fill=X,pady=2,padx=2)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.statustxt=StringVar()
        self.statustxt.set('未选择路径')
        self.createWidgets()
    def createWidgets(self):

        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        
        f00=Frame(f0)
        f00.pack(pady=5,fill=X,padx=5)
        Label(f00, text='回溯路径:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f00)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f00,text='选择回溯路径',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)


        f01=Frame(f0)
        f01.pack(fill=X,pady=2,padx=2)
        Label(f01, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)

        f02=Frame(f0)
        f02.pack(fill=X,pady=2,padx=2)
        self.bkbtn=Button(f02,text='回溯目录下所有一次铺货产品',fg='#ffffff',bg='green',command=lambda:Thread(target=self.back_data).start())
        self.bkbtn.pack(side=LEFT,padx=2, pady=2)

    
    def choosefile(self):

        select_path=filedialog.askdirectory()
        self.DaoRuIpt.delete(0, END)
        self.DaoRuIpt.insert(0,select_path)
        self.fpath=select_path
        cfs=os.listdir(select_path)
        self.itemids=[]
        for fname in cfs:
            cfpath=f'{select_path}/{fname}'
            wb=openpyxl.load_workbook(cfpath)
            ws=wb['Template']
            id_col=4
            for col in range(1,ws.max_column+1):
                if ws.cell(row=3,column=col).value.lower()=='parent sku':
                    id_col=col
                    break
            
            for row in range(7,ws.max_row+1):
                cur_itemid=ws.cell(row=row,column=id_col).value
                if cur_itemid and (cur_itemid not in self.itemids):
                    self.itemids.append(cur_itemid)
        self.statustxt.set(f'当前目录下共有 {len(self.itemids)} 个产品')
        
    def back_data(self):
        itemids=self.itemids
        if not self.DaoRuIpt.get().strip() or len(itemids)==0:
            messagebox.showinfo('提示','请先选择目录')
            return
    
        askys=messagebox.askyesno('提示',f'确定回溯 {len(itemids)} 个产品的状态值')
        if not askys:
            return
        self.bkbtn.config(state=DISABLED)

        itemidsStr=','.join([f"'{itemid}'" for itemid in itemids])
        sqlStr=f'update ProShopeePh set daochu=0 where daochu=1 and itemid in ({itemidsStr})'
        aff=self.ms.ExecNoQuery(sqlStr)

        print(f'回溯完毕,成功回溯 {aff} 行状态')
        messagebox.showinfo('提示',f'回溯完毕,成功回溯 {aff} 行状态')

        self.bkbtn.config(state=NORMAL)

import tkinter
list_win=tkinter.Tk()
list_win.wm_geometry("800x650")
list_win.title('普货导出')
AdminFra(list_win).pack(fill=X,padx=3,pady=3)
list_win.mainloop()